﻿using BLL;
using System;
using System.Web.UI.WebControls;

namespace Thksoft.Module.StatisticalReport
{
    public partial class MonthDataReport : BaseUI.BasePage
    {
        private QX_PLANYEARAPPLYBLL planYearApplayBLL = new QX_PLANYEARAPPLYBLL();
        private QS_PROJECTBLL projectBLL = new QS_PROJECTBLL();
        private QX_PLANYEAR_DETAILBLL planYearDetailBLL = new QX_PLANYEAR_DETAILBLL();
        private QS_PLANMONTHAPPLYBLL planMonthApplyBLL = new QS_PLANMONTHAPPLYBLL();
        private QS_PLANWEEKAPPLYBLL planWeekApplyBLL = new QS_PLANWEEKAPPLYBLL();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                var key = Request.QueryString["ID"];
                if (key == "")
                {
                    MessageBox.Show(this, "参数ID传递错误");
                    return;
                }
                Key = Convert.ToDecimal(key);
                BindData();
            }
        }

        public decimal Key
        {
            get
            {
                var text = FORM_KEY.Value;
                if (text == "")
                {
                    return -1;
                }
                return Convert.ToDecimal(text);
            }
            set
            {
                FORM_KEY.Value = value.ToString();
            }
        }

        protected void SORTING_EVENT(object sender, GridViewSortEventArgs e)
        {
            string sPage = e.SortExpression;
            string SortOrder, OrderDire;
            SortOrder = ViewState["ORDER"] == null ? null : (string)ViewState["ORDER"];
            OrderDire = ViewState["ORDER_TYPE"] == null ? null : (string)ViewState["ORDER_TYPE"];
            if (SortOrder == sPage)
            {
                if (OrderDire == "DESC")
                    OrderDire = "ASC";
                else
                    OrderDire = "DESC";
            }
            else
            {
                SortOrder = e.SortExpression;
                OrderDire = "ASC";
            }
            ViewState["ORDER"] = SortOrder;
            ViewState["ORDER_TYPE"] = OrderDire;
            BindData();
        }

        protected void BindData()
        {
            var entity = planMonthApplyBLL.Find(Key);

            #region sql注释

            //查询月 所有交易记录
            //            var sql = @"SELECT
            //	T1.N_WEEK,
            //	SUM(DECODE(T3.N_TYPE,1,T2.N_AMOUNT,0)) AS USER_AMOUNT_INTO,
            //	(SUM(DECODE(T3.N_TYPE,1,DECODE(T4.N_AMT,NULL,0,T4.N_AMT),0))/10000) AS USER_AMOUNT_INTO_ACTUAL,
            //	SUM(DECODE(T3.N_TYPE,1,1,0)) AS USER_AMOUNT_INTO_NUMBER,
            //	SUM(DECODE(T3.N_TYPE,1,DECODE(T4.N_AMT,NULL,0,1),0)) AS USER_AMOUNT_INTO_NUMBER_ACTUAL,
            //	SUM(DECODE(T3.N_TYPE,2,T2.N_AMOUNT,0)) AS USER_AMOUNT_OUT,
            //	(SUM(DECODE(T3.N_TYPE,2,DECODE(T4.N_AMT,NULL,0,T4.N_AMT),0))/10000) AS USER_AMOUNT_OUT_ACTUAL,
            //	SUM(DECODE(T3.N_TYPE,2,1,0)) AS USER_AMOUNT_OUT_NUMBER,
            //	SUM(DECODE(T3.N_TYPE,2,DECODE(T4.N_AMT,NULL,0,1),0)) AS USER_AMOUNT_OUT_NUMBER_ACTUAL
            //FROM
            //	QS_PLANWEEKAPPLY T1
            //INNER JOIN QS_PLANWEEK_DETAIL T2 ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID
            //INNER JOIN QS_PROJECT T3 ON T2.N_PROID = T3.N_PROID
            //LEFT JOIN YX_BANK_DETAILLIST T4
            //ON T2.C_PROWEEKNUM = T4.C_PROWEEKNUM
            //WHERE T1.N_MONTHAPP_ID = {0} AND T2.N_TYPE =2
            //GROUP BY
            //	T1.N_WEEK
            //ORDER BY T1.N_WEEK";

            /*string sql = @"select *
  from (select t.n_week xh,
               to_char(t.n_week) n_week,
               sum(decode(d.n_type2, 1, d.N_AMOUNT, 0)) into_amount,
               sum(decode(d.n_type2, 1, d.N_USEAMOUNT, 0)) into_use,

               decode(sum(decode(d.n_type2, 1, d.N_AMOUNT, 0)),
                      0, --分母为0
                      0,
                      round(sum(decode(d.n_type2, 1, d.N_USEAMOUNT, 0)) /
                            sum(decode(d.n_type2, 1, d.N_AMOUNT, 0)) * 100,
                            2)) into_lv,

               sum(decode(d.n_type2, 2, d.N_AMOUNT, 0)) out_amount,
               sum(decode(d.n_type2, 2, d.N_USEAMOUNT, 0)) out_use,

               decode(sum(decode(d.n_type2, 2, d.N_AMOUNT, 0)),
                      0,
                      0,
                      round(sum(decode(d.n_type2, 2, d.N_USEAMOUNT, 0)) /
                            sum(decode(d.n_type2, 2, d.N_AMOUNT, 0)) * 100,
                            2)) out_lv
          from qs_planweekapply t, qs_planweek_detail d
         where t.n_business_id <> 0
           and t.n_monthapp_id = {0}
           and t.n_business_state = 2
           and t.n_weekapp_id = d.n_weekapp_id
           and d.n_type = 0
         group by t.n_week

        union all
        select 10,
               '周合计',
               sum(decode(d.n_type2, 1, d.N_AMOUNT, 0)) into_amount,
               sum(decode(d.n_type2, 1, d.N_USEAMOUNT, 0)) into_use,

               decode(sum(decode(d.n_type2, 1, d.N_AMOUNT, 0)),
                      0, --分母为0
                      0,
                      round(sum(decode(d.n_type2, 1, d.N_USEAMOUNT, 0)) /
                            sum(decode(d.n_type2, 1, d.N_AMOUNT, 0)) * 100,
                            2)) into_lv,

               sum(decode(d.n_type2, 2, d.N_AMOUNT, 0)) out_amount,
               sum(decode(d.n_type2, 2, d.N_USEAMOUNT, 0)) out_use,

               decode(sum(decode(d.n_type2, 2, d.N_AMOUNT, 0)),
                      0,
                      0,
                      round(sum(decode(d.n_type2, 2, d.N_USEAMOUNT, 0)) /
                            sum(decode(d.n_type2, 2, d.N_AMOUNT, 0)) * 100,
                            2)) out_lv
          from qs_planweekapply t, qs_planweek_detail d
         where t.n_business_id <> 0
           and t.n_business_state = 2
           and t.n_monthapp_id = {0}
           and t.n_weekapp_id = d.n_weekapp_id
           and d.n_type = 0

        union all
        select 11,
               '月计划',

               sum(decode(t.n_type2, 1, t.N_AMOUNT, 0)) into_amount,

               null,
               null,
               sum(decode(t.n_type2, 2, t.N_AMOUNT, 0)) out_amount,
               null,
               null
          from qs_planmonth_detail t
         where t.n_monthapp_id = {0}
           and t.n_type = 0)
 order by xh "; */

            #endregion sql注释

            #region sql语句

            string sql = @"select xh,
       n_week,
       into_amount,
       into_use,
       into_lv,
       out_amount,
       out_use,
       out_lv,
       decode(amount_sum, null, '-', amount_sum) amount_sum,
       decode(amount_st, null, '-', amount_st) amount_st
  from (select xh,
               n_week,
               into_amount,
               into_use,
               into_lv,
               out_amount,
               out_use,
               out_lv,
               amount_st,
               amount_sum
          from (select t.n_week xh,
                       to_char(t.n_week) n_week,
                       sum(decode(d.n_type2, 1, d.N_AMOUNT, 0)) into_amount,
                       sum(decode(d.n_type2, 1, d.N_USEAMOUNT, 0)) into_use,

                       decode(sum(decode(d.n_type2, 1, d.N_AMOUNT, 0)),
                              0, --分母为0
                              0,
                              round(sum(decode(d.n_type2, 1, d.N_USEAMOUNT, 0)) /
                                    sum(decode(d.n_type2, 1, d.N_AMOUNT, 0)) * 100,
                                    2)) into_lv,

                       sum(decode(d.n_type2, 2, d.N_AMOUNT, 0)) out_amount,
                       sum(decode(d.n_type2, 2, d.N_USEAMOUNT, 0)) out_use,

                       decode(sum(decode(d.n_type2, 2, d.N_AMOUNT, 0)),
                              0,
                              0,
                              round(sum(decode(d.n_type2, 2, d.N_USEAMOUNT, 0)) /
                                    sum(decode(d.n_type2, 2, d.N_AMOUNT, 0)) * 100,
                                    2)) out_lv,
                       t.N_YEAR
                  from qs_planweekapply t, qs_planweek_detail d
                 where t.n_business_id <> 0
                   and t.n_monthapp_id = {0}
                   and t.n_business_state = 2
                   and t.n_weekapp_id = d.n_weekapp_id
                   and d.n_type = 0
                 group by t.n_week, t.N_YEAR) a
          left join (select sum(decode(u.n_business_state, 2, u.n_amount)) amount_st,
                           sum(u.n_amount) amount_sum,
                           u.d_data
                      from QS_URGENTFUNDSAPPLY u, qs_planweekapply t
                     where u.d_data between t.d_start and t.d_end
                       and u.n_company_id = t.n_company_id
                       and u.n_company_id = {3}
                       and to_char(u.d_data, 'yyyy') = '{1}'
                       and to_char(u.d_data, 'mm') = '{2}'
                     group by u.d_data) c
            on (a.N_YEAR = to_char(c.d_data, 'yyyy'))

        union all
        /**周合计**/
        select 10,
               '周合计',
               into_amount,
               into_use,
               into_lv,
               out_amount,
               out_use,
               out_lv,
               amount_st,
               amount_sum
          from (select /*10,
                                       '月合计',*/
                 sum(decode(d.n_type2, 1, d.N_AMOUNT, 0)) into_amount,
                 sum(decode(d.n_type2, 1, d.N_USEAMOUNT, 0)) into_use,

                 decode(sum(decode(d.n_type2, 1, d.N_AMOUNT, 0)),
                        0, --分母为0
                        0,
                        round(sum(decode(d.n_type2, 1, d.N_USEAMOUNT, 0)) /
                              sum(decode(d.n_type2, 1, d.N_AMOUNT, 0)) * 100,
                              2)) into_lv,

                 sum(decode(d.n_type2, 2, d.N_AMOUNT, 0)) out_amount,
                 sum(decode(d.n_type2, 2, d.N_USEAMOUNT, 0)) out_use,

                 decode(sum(decode(d.n_type2, 2, d.N_AMOUNT, 0)),
                        0,
                        0,
                        round(sum(decode(d.n_type2, 2, d.N_USEAMOUNT, 0)) /
                              sum(decode(d.n_type2, 2, d.N_AMOUNT, 0)) * 100,
                              2)) out_lv,
                 t.n_year
                  from qs_planweekapply t, qs_planweek_detail d
                 where t.n_business_id <> 0
                   and t.n_business_state = 2
                   and t.n_monthapp_id = {0}
                   and t.n_weekapp_id = d.n_weekapp_id
                   and d.n_type = 0
                 group by t.n_year) a
          left join (select sum(decode(u.n_business_state, 2, u.n_amount)) amount_st,
                            sum(u.n_amount) amount_sum,
                            u.d_data
                       from QS_URGENTFUNDSAPPLY u
                      where u.n_company_id = {3}
                        and to_char(u.d_data, 'yyyy') = '{1}'
                        and to_char(u.d_data, 'mm') = '{2}'
                      group by u.d_data) c
            on (a.N_YEAR = to_char(c.d_data, 'yyyy'))

        union all
        /**月计划**/
        select 11,
               '月计划',
               into_amount,
               null,
               null,
               out_amount,
               null,
               null,
               amount_st,
               amount_sum
          from (select sum(decode(t.n_type2, 1, t.N_AMOUNT, 0)) into_amount,
                       sum(decode(t.n_type2, 2, t.N_AMOUNT, 0)) out_amount,
                       t1.n_year
                  from qs_planmonth_detail t, qs_planmonthapply t1
                 where t.n_monthapp_id = {0}
                   and t.n_type = 0
                   and t1.n_monthapp_id = t.n_monthapp_id
                 group by t1.n_year) a
          left join (select sum(decode(u.n_business_state, 2, u.n_amount)) amount_st,
                            sum(u.n_amount) amount_sum,
                            u.d_data
                       from QS_URGENTFUNDSAPPLY u
                      where u.n_company_id = {3}
                        and to_char(u.d_data, 'yyyy') = '{1}'
                        and to_char(u.d_data, 'mm') = '{2}'
                      group by u.d_data) c
            on (a.N_YEAR = to_char(c.d_data, 'yyyy')))
 order by xh";

            #endregion sql语句

            var month = "";
            if (entity.N_MONTH < 10)
            {
                month = "0" + entity.N_MONTH.ToString();
            }
            else
            {
                month = entity.N_MONTH.ToString();
            }
            sql = string.Format(sql, entity.N_MONTHAPP_ID, entity.N_YEAR, month, entity.N_COMPANY_ID);
            var dt = planYearDetailBLL.Query(sql);
            TABLE.DataSource = dt;
            TABLE.DataBind();

            Label1.Text = entity.C_MONTH_NAME + "月资金计划";
        }

        protected void btnDaoChu_Click(object sender, EventArgs e)
        {
            toExecl1(Panel1, Label1.Text + DateTime.Now.ToShortDateString());
        }
    }
}